<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>MySQL📦DQL查询数据 | 小李博客</title><meta name="keywords" content="MySQL"><meta name="author" content="小李博客"><meta name="copyright" content="小李博客"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="DQL语言 DQL( Data Query Language 数据查询语言 ) 查询数据库数据 , 如SELECT语句 简单的单表查询或多表的复杂查询和嵌套查询 是数据库语言中最核心，最重要的语句 使用频率最高的语句    Select语法12345678910SELECT [ALL | DISTINCT]&amp;#123;* | table.* | [table.field1[as alias1][,">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL📦DQL查询数据">
<meta property="og:url" content="http://xiaoliblog.cn/page/MySQL04.html">
<meta property="og:site_name" content="小李博客">
<meta property="og:description" content="DQL语言 DQL( Data Query Language 数据查询语言 ) 查询数据库数据 , 如SELECT语句 简单的单表查询或多表的复杂查询和嵌套查询 是数据库语言中最核心，最重要的语句 使用频率最高的语句    Select语法12345678910SELECT [ALL | DISTINCT]&amp;#123;* | table.* | [table.field1[as alias1][,">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png">
<meta property="article:published_time" content="2021-03-19T12:34:22.896Z">
<meta property="article:modified_time" content="2021-04-11T08:56:13.832Z">
<meta property="article:author" content="小李博客">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png"><link rel="shortcut icon" href="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png"><link rel="canonical" href="http://xiaoliblog.cn/page/MySQL04"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.js" defer></script><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: {"appId":"RTG4CPNBLJ","apiKey":"f1745bdad68ceec57653b78244fe332c","indexName":"MyBlogIndex","hits":{"per_page":6},"languages":{"input_placeholder":"搜索文章","hits_empty":"找不到您查询的内容：${query}","hits_stats":"找到 ${hits} 条结果，用时 ${time} 毫秒"}},
  localSearch: undefined,
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":200},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":100,"languages":{"author":"作者: 小李博客","link":"链接: ","source":"来源: 小李博客","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"top-center"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-04-11 16:56:13'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    })(window)</script><link rel="stylesheet" href="/css/MyStyle/MyStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="/css/MyStyle/tagStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/zykjofficial/zykjresource@master/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/plugins.min.css" media="defer" onload="this.media='all'"><meta name="generator" content="Hexo 5.2.0"><link rel="alternate" href="/atom.xml" title="小李博客" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">小李博客</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">MySQL📦DQL查询数据</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-03-19T12:34:22.896Z" title="发表于 2021-03-19 20:34:22">2021-03-19</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-04-11T08:56:13.832Z" title="更新于 2021-04-11 16:56:13">2021-04-11</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><i class="fas fa-angle-right post-meta-separator"></i><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/MySQL/">MySQL</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">3.5k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>14分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="MySQL📦DQL查询数据"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div><article class="post-content" id="article-container"><h1 id="DQL语言"><a href="#DQL语言" class="headerlink" title="DQL语言"></a>DQL语言</h1><ul>
<li><code>DQL</code>( Data Query Language 数据查询语言 )<ul>
<li>查询数据库数据 , 如SELECT语句</li>
<li>简单的单表查询或多表的复杂查询和嵌套查询</li>
<li>是数据库语言中最核心，最重要的语句</li>
<li>使用频率最高的语句</li>
</ul>
</li>
</ul>
<h2 id="Select语法"><a href="#Select语法" class="headerlink" title="Select语法"></a>Select语法</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> [<span class="keyword">ALL</span> | <span class="keyword">DISTINCT</span>]</span><br><span class="line">&#123;* | table.* | [table.field1[<span class="keyword">as</span> alias1][,table.field2[<span class="keyword">as</span> alias2]][,...]]&#125;</span><br><span class="line"><span class="keyword">FROM</span> table_name [<span class="keyword">as</span> table_alias]</span><br><span class="line">  [<span class="keyword">left</span> | <span class="keyword">right</span> | <span class="keyword">inner</span> <span class="keyword">join</span> table_name2]  <span class="comment">-- 联合查询</span></span><br><span class="line">  [<span class="keyword">WHERE</span> ...]  <span class="comment">-- 指定结果需满足的条件</span></span><br><span class="line">  [<span class="keyword">GROUP</span> <span class="keyword">BY</span> ...]  <span class="comment">-- 指定结果按照哪几个字段来分组</span></span><br><span class="line">  [<span class="keyword">HAVING</span>]  <span class="comment">-- 过滤分组的记录必须满足的次要条件</span></span><br><span class="line">  [<span class="keyword">ORDER</span> <span class="keyword">BY</span> ...]  <span class="comment">-- 指定查询记录按一个或多个条件排序</span></span><br><span class="line">  [<span class="keyword">LIMIT</span> &#123;[<span class="keyword">offset</span>,]<span class="keyword">row_count</span> | row_countOFFSET <span class="keyword">offset</span>&#125;];</span><br><span class="line">   <span class="comment">-- 指定查询的记录从哪条至哪条</span></span><br></pre></td></tr></table></figure>

<div class="note warning simple"><p>注意 : [ ] 括号代表可选的 , { }括号代表必选得</p>
</div>

<h2 id="测试环境搭建"><a href="#测试环境搭建" class="headerlink" title="测试环境搭建"></a>测试环境搭建</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="keyword">IF</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> <span class="string">`school`</span>;</span><br><span class="line"><span class="comment">-- 创建一个school数据库</span></span><br><span class="line"><span class="keyword">USE</span> <span class="string">`school`</span>;<span class="comment">-- 创建学生表</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`student`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`student`</span>(</span><br><span class="line">	<span class="string">`studentno`</span> <span class="built_in">INT</span>(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;学号&#x27;</span>,</span><br><span class="line">    <span class="string">`loginpwd`</span> <span class="built_in">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`studentname`</span> <span class="built_in">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;学生姓名&#x27;</span>,</span><br><span class="line">    <span class="string">`sex`</span> <span class="built_in">TINYINT</span>(<span class="number">1</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;性别，0或1&#x27;</span>,</span><br><span class="line">    <span class="string">`gradeid`</span> <span class="built_in">INT</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;年级编号&#x27;</span>,</span><br><span class="line">    <span class="string">`phone`</span> <span class="built_in">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;联系电话，允许为空&#x27;</span>,</span><br><span class="line">    <span class="string">`address`</span> <span class="built_in">VARCHAR</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;地址，允许为空&#x27;</span>,</span><br><span class="line">    <span class="string">`borndate`</span> DATETIME <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;出生时间&#x27;</span>,</span><br><span class="line">    <span class="string">`email`</span> <span class="built_in">VARCHAR</span> (<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;邮箱账号允许为空&#x27;</span>,</span><br><span class="line">    <span class="string">`identitycard`</span> <span class="built_in">VARCHAR</span>(<span class="number">18</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;身份证号&#x27;</span>,</span><br><span class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`studentno`</span>),</span><br><span class="line">    <span class="keyword">UNIQUE</span> <span class="keyword">KEY</span> <span class="string">`identitycard`</span>(<span class="string">`identitycard`</span>),</span><br><span class="line">    <span class="keyword">KEY</span> <span class="string">`email`</span> (<span class="string">`email`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span>=MYISAM <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建年级表</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`grade`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`grade`</span>(</span><br><span class="line">	<span class="string">`gradeid`</span> <span class="built_in">INT</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">&#x27;年级编号&#x27;</span>,</span><br><span class="line">  <span class="string">`gradename`</span> <span class="built_in">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;年级名称&#x27;</span>,</span><br><span class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`gradeid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> AUTO_INCREMENT = <span class="number">6</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span> = utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建科目表</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`subject`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`subject`</span>(</span><br><span class="line">	<span class="string">`subjectno`</span><span class="built_in">INT</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">&#x27;课程编号&#x27;</span>,</span><br><span class="line">    <span class="string">`subjectname`</span> <span class="built_in">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;课程名称&#x27;</span>,</span><br><span class="line">    <span class="string">`classhour`</span> <span class="built_in">INT</span>(<span class="number">4</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;学时&#x27;</span>,</span><br><span class="line">    <span class="string">`gradeid`</span> <span class="built_in">INT</span>(<span class="number">4</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;年级编号&#x27;</span>,</span><br><span class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`subjectno`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span> = <span class="keyword">INNODB</span> AUTO_INCREMENT = <span class="number">19</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span> = utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建成绩表</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`result`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`result`</span>(</span><br><span class="line">	<span class="string">`studentno`</span> <span class="built_in">INT</span>(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;学号&#x27;</span>,</span><br><span class="line">    <span class="string">`subjectno`</span> <span class="built_in">INT</span>(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;课程编号&#x27;</span>,</span><br><span class="line">    <span class="string">`examdate`</span> DATETIME <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;考试日期&#x27;</span>,</span><br><span class="line">    <span class="string">`studentresult`</span> <span class="built_in">INT</span> (<span class="number">4</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;考试成绩&#x27;</span>,</span><br><span class="line">    <span class="keyword">KEY</span> <span class="string">`subjectno`</span> (<span class="string">`subjectno`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span> = <span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span> = utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入学生数据 其余自行添加 这里只添加了2行</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`student`</span> (<span class="string">`studentno`</span>,<span class="string">`loginpwd`</span>,<span class="string">`studentname`</span>,<span class="string">`sex`</span>,<span class="string">`gradeid`</span>,<span class="string">`phone`</span>,<span class="string">`address`</span>,<span class="string">`borndate`</span>,<span class="string">`email`</span>,<span class="string">`identitycard`</span>)</span><br><span class="line"><span class="keyword">VALUES</span></span><br><span class="line">(<span class="number">1000</span>,<span class="string">&#x27;123456&#x27;</span>,<span class="string">&#x27;张伟&#x27;</span>,<span class="number">0</span>,<span class="number">2</span>,<span class="string">&#x27;13800001234&#x27;</span>,<span class="string">&#x27;北京朝阳&#x27;</span>,<span class="string">&#x27;1980-1-1&#x27;</span>,<span class="string">&#x27;text123@qq.com&#x27;</span>,<span class="string">&#x27;123456198001011234&#x27;</span>),</span><br><span class="line">(<span class="number">1001</span>,<span class="string">&#x27;123456&#x27;</span>,<span class="string">&#x27;赵强&#x27;</span>,<span class="number">1</span>,<span class="number">3</span>,<span class="string">&#x27;13800002222&#x27;</span>,<span class="string">&#x27;广东深圳&#x27;</span>,<span class="string">&#x27;1990-1-1&#x27;</span>,<span class="string">&#x27;text111@qq.com&#x27;</span>,<span class="string">&#x27;123456199001011233&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入成绩数据  这里仅插入了一组，其余自行添加</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`result`</span>(<span class="string">`studentno`</span>,<span class="string">`subjectno`</span>,<span class="string">`examdate`</span>,<span class="string">`studentresult`</span>)</span><br><span class="line"><span class="keyword">VALUES</span></span><br><span class="line">(<span class="number">1000</span>,<span class="number">1</span>,<span class="string">&#x27;2013-11-11 16:00:00&#x27;</span>,<span class="number">85</span>),</span><br><span class="line">(<span class="number">1000</span>,<span class="number">2</span>,<span class="string">&#x27;2013-11-12 16:00:00&#x27;</span>,<span class="number">70</span>),</span><br><span class="line">(<span class="number">1000</span>,<span class="number">3</span>,<span class="string">&#x27;2013-11-11 09:00:00&#x27;</span>,<span class="number">68</span>),</span><br><span class="line">(<span class="number">1000</span>,<span class="number">4</span>,<span class="string">&#x27;2013-11-13 16:00:00&#x27;</span>,<span class="number">98</span>),</span><br><span class="line">(<span class="number">1000</span>,<span class="number">5</span>,<span class="string">&#x27;2013-11-14 16:00:00&#x27;</span>,<span class="number">58</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入年级数据</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`grade`</span> (<span class="string">`gradeid`</span>,<span class="string">`gradename`</span>) <span class="keyword">VALUES</span>(<span class="number">1</span>,<span class="string">&#x27;大一&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;大二&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;大三&#x27;</span>),(<span class="number">4</span>,<span class="string">&#x27;大四&#x27;</span>),(<span class="number">5</span>,<span class="string">&#x27;预科班&#x27;</span>);</span><br><span class="line"></span><br><span class="line">FullHouse回复 @白条asd  :4</span><br><span class="line">“</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入科目数据</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`subject`</span>(<span class="string">`subjectno`</span>,<span class="string">`subjectname`</span>,<span class="string">`classhour`</span>,<span class="string">`gradeid`</span>)<span class="keyword">VALUES</span></span><br><span class="line">(<span class="number">1</span>,<span class="string">&#x27;高等数学-1&#x27;</span>,<span class="number">110</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">2</span>,<span class="string">&#x27;高等数学-2&#x27;</span>,<span class="number">110</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="string">&#x27;高等数学-3&#x27;</span>,<span class="number">100</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="string">&#x27;高等数学-4&#x27;</span>,<span class="number">130</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">5</span>,<span class="string">&#x27;C语言-1&#x27;</span>,<span class="number">110</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">6</span>,<span class="string">&#x27;C语言-2&#x27;</span>,<span class="number">110</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="number">7</span>,<span class="string">&#x27;C语言-3&#x27;</span>,<span class="number">100</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="number">8</span>,<span class="string">&#x27;C语言-4&#x27;</span>,<span class="number">130</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">9</span>,<span class="string">&#x27;Java程序设计-1&#x27;</span>,<span class="number">110</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">10</span>,<span class="string">&#x27;Java程序设计-2&#x27;</span>,<span class="number">110</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="number">11</span>,<span class="string">&#x27;Java程序设计-3&#x27;</span>,<span class="number">100</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="number">12</span>,<span class="string">&#x27;Java程序设计-4&#x27;</span>,<span class="number">130</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">13</span>,<span class="string">&#x27;数据库结构-1&#x27;</span>,<span class="number">110</span>,<span class="number">1</span>),</span><br><span class="line">(<span class="number">14</span>,<span class="string">&#x27;数据库结构-2&#x27;</span>,<span class="number">110</span>,<span class="number">2</span>),</span><br><span class="line">(<span class="number">15</span>,<span class="string">&#x27;数据库结构-3&#x27;</span>,<span class="number">100</span>,<span class="number">3</span>),</span><br><span class="line">(<span class="number">16</span>,<span class="string">&#x27;数据库结构-4&#x27;</span>,<span class="number">130</span>,<span class="number">4</span>),</span><br><span class="line">(<span class="number">17</span>,<span class="string">&#x27;C#基础&#x27;</span>,<span class="number">130</span>,<span class="number">1</span>);</span><br></pre></td></tr></table></figure>

<h1 id="指定查询字段"><a href="#指定查询字段" class="headerlink" title="指定查询字段"></a>指定查询字段</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询表中所有的数据列结果 , 采用 **&quot; \* &quot;** 符号; 但是效率低，不推荐 .</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询所有学生信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询指定列(学号 , 姓名)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student;</span><br></pre></td></tr></table></figure>

<h2 id="AS-子句"><a href="#AS-子句" class="headerlink" title="AS 子句"></a>AS 子句</h2><ul>
<li><code>AS</code>子句作用<ul>
<li>可给数据列取一个新别名</li>
<li>可给表取一个新别名</li>
<li>可把经计算或总结的结果用另一个新名称来代替</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 这里是为列取别名(当然as关键词可以省略)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno <span class="keyword">AS</span> 学号,studentname <span class="keyword">AS</span> 姓名 <span class="keyword">FROM</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 使用as也可以为表取别名</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno <span class="keyword">AS</span> 学号,studentname <span class="keyword">AS</span> 姓名 <span class="keyword">FROM</span> student <span class="keyword">AS</span> s;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 使用as,为查询结果取一个新名字</span></span><br><span class="line"><span class="comment">-- CONCAT()函数拼接字符串</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">CONCAT</span>(<span class="string">&#x27;姓名:&#x27;</span>,studentname) <span class="keyword">AS</span> 新姓名 <span class="keyword">FROM</span> student;</span><br></pre></td></tr></table></figure>
<h2 id="DISTINCT关键字"><a href="#DISTINCT关键字" class="headerlink" title="DISTINCT关键字"></a>DISTINCT关键字</h2><ul>
<li>DISTINCT关键字作用<ul>
<li>去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条 </li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- # 查看哪些同学参加了考试(学号) 去除重复项</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> <span class="keyword">result</span>; <span class="comment">-- 查看考试成绩</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno <span class="keyword">FROM</span> <span class="keyword">result</span>; <span class="comment">-- 查看哪些同学参加了考试</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> studentno <span class="keyword">FROM</span> <span class="keyword">result</span>; <span class="comment">-- 了解:DISTINCT 去除重复项 , (默认是ALL)</span></span><br></pre></td></tr></table></figure>

<h2 id="使用表达式的列"><a href="#使用表达式的列" class="headerlink" title="使用表达式的列"></a>使用表达式的列</h2><ul>
<li>数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成</li>
<li>应用场景 :<ul>
<li>SELECT语句返回结果列中使用</li>
<li>SELECT语句中的ORDER BY , HAVING等子句中使用</li>
<li>DML语句中的 where 条件语句中使用表达式</li>
<li>避免SQL返回结果中包含 ‘ <code>.</code> ‘ , ‘ <code>*</code> ‘ 和括号等干扰开发语言程序.</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- selcet查询中可以使用表达式</span></span><br><span class="line"><span class="keyword">SELECT</span> @@auto_increment_increment; <span class="comment">-- 查询自增步长</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">VERSION</span>(); <span class="comment">-- 查询版本号</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="number">100</span>*<span class="number">3</span><span class="number">-1</span> <span class="keyword">AS</span> 计算结果; <span class="comment">-- 表达式</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 学员考试成绩集体提分一分查看</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,StudentResult+<span class="number">1</span> <span class="keyword">AS</span> <span class="string">&#x27;提分后&#x27;</span> <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br></pre></td></tr></table></figure>

<h1 id="where条件语句"><a href="#where条件语句" class="headerlink" title="where条件语句"></a>where条件语句</h1><ul>
<li>作用：用于检索数据表中 符合条件 的记录</li>
<li>搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.</li>
</ul>
<h2 id="逻辑操作符"><a href="#逻辑操作符" class="headerlink" title="逻辑操作符"></a>逻辑操作符</h2><p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@ede145a0fdfdb6023ec2740e8ea833c842265d5a/2021/04/11/0c06d6eaf83b3872c71ea734d3afaf0d.png"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 满足条件的查询(where)</span></span><br><span class="line"><span class="keyword">SELECT</span> Studentno,StudentResult <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询考试成绩在95-100之间的</span></span><br><span class="line"><span class="keyword">SELECT</span> Studentno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> StudentResult&gt;=<span class="number">95</span> <span class="keyword">AND</span> StudentResult&lt;=<span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- AND也可以写成 &amp;&amp;</span></span><br><span class="line"><span class="keyword">SELECT</span> Studentno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> StudentResult&gt;=<span class="number">95</span> &amp;&amp; StudentResult&lt;=<span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 模糊查询(对应的词:精确查询)</span></span><br><span class="line"><span class="keyword">SELECT</span> Studentno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> StudentResult <span class="keyword">BETWEEN</span> <span class="number">95</span> <span class="keyword">AND</span> <span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 除了1000号同学,要其他同学的成绩</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentresult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> studentno!=<span class="number">1000</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 使用NOT</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentresult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">NOT</span> studentno=<span class="number">1000</span>;</span><br></pre></td></tr></table></figure>

<h2 id="比较操作符"><a href="#比较操作符" class="headerlink" title="比较操作符"></a>比较操作符</h2><p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@649dc11e08e6d66aa3606669d747a31bc439e090/2021/04/11/1a996f5097844db6446fe34c56d3ee1c.png"></p>
<ul>
<li>数值数据类型的记录之间才能进行算术运算 ;</li>
<li>相同数据类型的数据之间才能进行比较 </li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 模糊查询 between and \ like \ in \ null</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- LIKE</span></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- 查询姓刘的同学的学号及姓名</span></span><br><span class="line"><span class="comment">-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">&#x27;刘%&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓刘的同学,后面只有一个字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">&#x27;刘_&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓刘的同学,后面只有两个字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">&#x27;刘__&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓名中含有 嘉 字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">&#x27;%嘉%&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓名中含有特殊字符的需要使用转义符号 &#x27;\&#x27;</span></span><br><span class="line"><span class="comment">-- 自定义转义符关键字: ESCAPE &#x27;:&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- IN</span></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- 查询学号为1000,1001,1002的学生姓名</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentno <span class="keyword">IN</span> (<span class="number">1000</span>,<span class="number">1001</span>,<span class="number">1002</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询地址在北京,南京,河南洛阳的学生</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname,address <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> address <span class="keyword">IN</span> (<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;南京&#x27;</span>,<span class="string">&#x27;河南洛阳&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- NULL 空</span></span><br><span class="line"><span class="comment">-- =============================================</span></span><br><span class="line"><span class="comment">-- 查询出生日期没有填写的同学</span></span><br><span class="line"><span class="comment">-- 不能直接写=NULL , 这是代表错误的 , 用 is null</span></span><br><span class="line"><span class="keyword">SELECT</span> studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> BornDate <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询出生日期填写的同学</span></span><br><span class="line"><span class="keyword">SELECT</span> studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> BornDate <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询没有写家庭住址的同学(空字符串不等于null)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> Address=<span class="string">&#x27;&#x27;</span> <span class="keyword">OR</span> Address <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br></pre></td></tr></table></figure>

<h1 id="连接查询"><a href="#连接查询" class="headerlink" title="连接查询"></a>连接查询</h1><h2 id="JOIN-对比"><a href="#JOIN-对比" class="headerlink" title="JOIN 对比"></a>JOIN 对比</h2><p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@63ff39941966b509c7b6e8e43a8357d6f7d75eab/2021/04/11/c145003bae58aac9c318734426dd3339.png"></p>
<img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@ba9aee79901e2d7da80b053c5c13f57e481e696c/2021/04/11/0e3576579644bd4ad95e2f784cc5ac0e.png" width="50%">

<ul>
<li>七种Join</li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@99742fd6f0526a23bdcab3bde229dfc5c96f48f0/2021/04/11/89fa9016e5080d6749590dccff001f26.png" width="100%">

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">连接查询</span></span><br><span class="line"><span class="comment">   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询</span></span><br><span class="line"><span class="comment">内连接 inner join</span></span><br><span class="line"><span class="comment">   查询两个表中的结果集中的交集</span></span><br><span class="line"><span class="comment">外连接 outer join</span></span><br><span class="line"><span class="comment">   左外连接 left join</span></span><br><span class="line"><span class="comment">       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)</span></span><br><span class="line"><span class="comment">   右外连接 right join</span></span><br><span class="line"><span class="comment">       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)</span></span><br><span class="line"><span class="comment">       </span></span><br><span class="line"><span class="comment">等值连接和非等值连接</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">自连接</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> student;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*思路:</span></span><br><span class="line"><span class="comment">(1):分析需求,确定查询的列来源于两个类,student result,连接查询</span></span><br><span class="line"><span class="comment">(2):确定使用哪种连接查询?(内连接)</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 右连接(也可实现)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 等值连接</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s , <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">WHERE</span> r.studentno = s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 左连接 (查询了所有同学,不考试的也会查出来)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查一下缺考的同学(左连接应用场景)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">WHERE</span> StudentResult <span class="keyword">IS</span> <span class="literal">NULL</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> sub.subjectno = r.subjectno</span><br></pre></td></tr></table></figure>

<h1 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">自连接</span></span><br><span class="line"><span class="comment">   数据表与自身进行连接</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中</span></span><br><span class="line"><span class="comment">    查询父栏目名称和其他子栏目名称</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建一个表</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`category`</span> (</span><br><span class="line"><span class="string">`categoryid`</span> <span class="built_in">INT</span>(<span class="number">10</span>) <span class="keyword">UNSIGNED</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">&#x27;主题id&#x27;</span>,</span><br><span class="line"><span class="string">`pid`</span> <span class="built_in">INT</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;父id&#x27;</span>,</span><br><span class="line"><span class="string">`categoryName`</span> <span class="built_in">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">&#x27;主题名字&#x27;</span>,</span><br><span class="line">PRIMARY <span class="keyword">KEY</span> (<span class="string">`categoryid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> AUTO_INCREMENT=<span class="number">9</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入数据</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryName`</span>)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">&#x27;2&#x27;</span>,<span class="string">&#x27;1&#x27;</span>,<span class="string">&#x27;信息技术&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;3&#x27;</span>,<span class="string">&#x27;1&#x27;</span>,<span class="string">&#x27;软件开发&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;4&#x27;</span>,<span class="string">&#x27;3&#x27;</span>,<span class="string">&#x27;数据库&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;5&#x27;</span>,<span class="string">&#x27;1&#x27;</span>,<span class="string">&#x27;美术设计&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;6&#x27;</span>,<span class="string">&#x27;3&#x27;</span>,<span class="string">&#x27;web开发&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;7&#x27;</span>,<span class="string">&#x27;5&#x27;</span>,<span class="string">&#x27;ps技术&#x27;</span>),</span><br><span class="line">(<span class="string">&#x27;8&#x27;</span>,<span class="string">&#x27;2&#x27;</span>,<span class="string">&#x27;办公信息&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)</span></span><br><span class="line"><span class="comment">-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)</span></span><br><span class="line"><span class="keyword">SELECT</span> a.categoryName <span class="keyword">AS</span> <span class="string">&#x27;父栏目&#x27;</span>,b.categoryName <span class="keyword">AS</span> <span class="string">&#x27;子栏目&#x27;</span></span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">category</span> <span class="keyword">AS</span> a,<span class="keyword">category</span> <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">WHERE</span> a.<span class="string">`categoryid`</span>=b.<span class="string">`pid`</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> sub.subjectno = r.subjectno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询学员及所属的年级(学号,学生姓名,年级名)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno <span class="keyword">AS</span> 学号,studentname <span class="keyword">AS</span> 学生姓名,gradename <span class="keyword">AS</span> 年级名称</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> grade g</span><br><span class="line"><span class="keyword">ON</span> s.<span class="string">`GradeId`</span> = g.<span class="string">`GradeID`</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询科目及所属的年级(科目名称,年级名称)</span></span><br><span class="line"><span class="keyword">SELECT</span> subjectname <span class="keyword">AS</span> 科目名称,gradename <span class="keyword">AS</span> 年级名称</span><br><span class="line"><span class="keyword">FROM</span> SUBJECT sub</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> grade g</span><br><span class="line"><span class="keyword">ON</span> sub.gradeid = g.gradeid</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname=<span class="string">&#x27;数据库结构-1&#x27;</span></span><br></pre></td></tr></table></figure>

<h1 id="排序和分页"><a href="#排序和分页" class="headerlink" title="排序和分页"></a>排序和分页</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*============== 排序 ================</span></span><br><span class="line"><span class="comment">语法 : ORDER BY</span></span><br><span class="line"><span class="comment">   ORDER BY 语句用于根据指定的列对结果集进行排序。</span></span><br><span class="line"><span class="comment">   ORDER BY 语句默认按照ASC升序对记录进行排序。</span></span><br><span class="line"><span class="comment">   如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。</span></span><br><span class="line"><span class="comment">   </span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)</span></span><br><span class="line"><span class="comment">-- 按成绩降序排序</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname=<span class="string">&#x27;数据库结构-1&#x27;</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*============== 分页 ================</span></span><br><span class="line"><span class="comment">语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset</span></span><br><span class="line"><span class="comment">好处 : (用户体验,网络传输,查询压力)</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">推导:</span></span><br><span class="line"><span class="comment">   第一页 : limit 0,5</span></span><br><span class="line"><span class="comment">   第二页 : limit 5,5</span></span><br><span class="line"><span class="comment">   第三页 : limit 10,5</span></span><br><span class="line"><span class="comment">   ......</span></span><br><span class="line"><span class="comment">   第N页 : limit (pageNo-1)*pageSzie,pageSzie</span></span><br><span class="line"><span class="comment">   [pageNo:页码,pageSize:单页面显示条数]</span></span><br><span class="line"><span class="comment">   </span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 每页显示5条数据</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname=<span class="string">&#x27;数据库结构-1&#x27;</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span> , studentno</span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">0</span>,<span class="number">5</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname=<span class="string">&#x27;JAVA第一学年&#x27;</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span></span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">0</span>,<span class="number">10</span></span><br></pre></td></tr></table></figure>

<h1 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*============== 子查询 ================</span></span><br><span class="line"><span class="comment">什么是子查询?</span></span><br><span class="line"><span class="comment">   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句</span></span><br><span class="line"><span class="comment">   嵌套查询可由多个子查询组成,求解的方式是由里及外;</span></span><br><span class="line"><span class="comment">   子查询返回的结果一般都是集合,故而建议使用IN关键字;</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列</span></span><br><span class="line"><span class="comment">-- 方法一:使用连接查询</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,r.subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> r.<span class="string">`SubjectNo`</span>=sub.<span class="string">`SubjectNo`</span></span><br><span class="line"><span class="keyword">WHERE</span> subjectname = <span class="string">&#x27;数据库结构-1&#x27;</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> studentresult <span class="keyword">DESC</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法二:使用子查询(执行顺序:由里及外)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> subjectno=(</span><br><span class="line">   <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> <span class="string">`subject`</span></span><br><span class="line">   <span class="keyword">WHERE</span> subjectname = <span class="string">&#x27;数据库结构-1&#x27;</span></span><br><span class="line">)</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> studentresult <span class="keyword">DESC</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名</span></span><br><span class="line"><span class="comment">-- 方法一:使用连接查询</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> s.<span class="string">`StudentNo`</span> = r.<span class="string">`StudentNo`</span></span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="string">`subject`</span> sub</span><br><span class="line"><span class="keyword">ON</span> sub.<span class="string">`SubjectNo`</span> = r.<span class="string">`SubjectNo`</span></span><br><span class="line"><span class="keyword">WHERE</span> subjectname = <span class="string">&#x27;高等数学-2&#x27;</span> <span class="keyword">AND</span> StudentResult&gt;=<span class="number">80</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法二:使用连接查询+子查询</span></span><br><span class="line"><span class="comment">-- 分数不小于80分的学生的学号和姓名</span></span><br><span class="line"><span class="keyword">SELECT</span> r.studentno,studentname <span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r <span class="keyword">ON</span> s.<span class="string">`StudentNo`</span>=r.<span class="string">`StudentNo`</span></span><br><span class="line"><span class="keyword">WHERE</span> StudentResult&gt;=<span class="number">80</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 在上面SQL基础上,添加需求:课程为 高等数学-2</span></span><br><span class="line"><span class="keyword">SELECT</span> r.studentno,studentname <span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r <span class="keyword">ON</span> s.<span class="string">`StudentNo`</span>=r.<span class="string">`StudentNo`</span></span><br><span class="line"><span class="keyword">WHERE</span> StudentResult&gt;=<span class="number">80</span> <span class="keyword">AND</span> subjectno=(</span><br><span class="line">   <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> <span class="string">`subject`</span></span><br><span class="line">   <span class="keyword">WHERE</span> subjectname = <span class="string">&#x27;高等数学-2&#x27;</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法三:使用子查询</span></span><br><span class="line"><span class="comment">-- 分步写简单sql语句,然后将其嵌套起来</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> studentno <span class="keyword">IN</span>(</span><br><span class="line">   <span class="keyword">SELECT</span> studentno <span class="keyword">FROM</span> <span class="keyword">result</span> <span class="keyword">WHERE</span> StudentResult&gt;=<span class="number">80</span> <span class="keyword">AND</span> subjectno=(</span><br><span class="line">       <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> <span class="string">`subject`</span> <span class="keyword">WHERE</span> subjectname = <span class="string">&#x27;高等数学-2&#x27;</span></span><br><span class="line">  )</span><br><span class="line">)</span><br></pre></td></tr></table></figure></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">小李博客</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://xiaoliblog.cn">https://xiaoliblog.cn</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://xiaoliblog.cn" target="_blank">小李博客</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySQL/">MySQL</a></div><div class="post_share"><div class="social-share" data-image="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/wechat.png" target="_blank"><img class="post-qr-code-img" src="/img/wechat.png" alt="微信"/></a><div class="post-qr-code-desc">微信</div></li><li class="reward-item"><a href="/img/alipay.png" target="_blank"><img class="post-qr-code-img" src="/img/alipay.png" alt="支付宝"/></a><div class="post-qr-code-desc">支付宝</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/page/MySQL05.html"><img class="prev-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">MySQL📦MySQL函数</div></div></a></div><div class="next-post pull-right"><a href="/page/MySQL03.html"><img class="next-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">MySQL📦DML语言</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/page/MySQL03.html" title="MySQL📦DML语言"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦DML语言</div></div></a></div><div><a href="/page/MySQL05.html" title="MySQL📦MySQL函数"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦MySQL函数</div></div></a></div><div><a href="/page/MySQL01.html" title="MySQL📦MySQL概述及环境配置"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦MySQL概述及环境配置</div></div></a></div><div><a href="/page/MySQL06.html" title="MySQL📦事务和索引"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦事务和索引</div></div></a></div><div><a href="/page/MySQL02.html" title="MySQL📦数据库操作"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦数据库操作</div></div></a></div><div><a href="/page/MySQL07.html" title="MySQL📦权限和如何设计数据库"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@9b917976d0ccd8f5d6aa4dcd36493a3885cf9116/2021/03/17/6ade69ffd58af23143fb6ce08e03eccd.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-03-19</div><div class="title">MySQL📦权限和如何设计数据库</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">小李博客</div><div class="author-info__description">越努力，越幸运！</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xiaoliblog"><i class="fab fa-github"></i><span>博主的GitHub首页</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/xiaoliblog" target="_blank" title="Gitee"><i class="iconfont icon-gitee card_icon_gitee"></i></a><a class="social-icon" href="https://space.bilibili.com/390969485" target="_blank" title="BiliBili"><i class="iconfont icon-bilibili card_icon_bilibili"></i></a><a class="social-icon" href="http://wpa.qq.com/msgrd?v=3&amp;uin=2312057536&amp;site=CSDN&amp;menu=yes" target="_blank" title="QQ"><i class="iconfont icon-qq card_icon_qq"></i></a><a class="social-icon" href="https://github.com/xiaoliblog" target="_blank" title="GitHub"><i class="iconfont icon-git card_icon_git"></i></a><a class="social-icon" href="https://blog.csdn.net/qq_43266250?spm=1010.2135.3001.5113" target="_blank" title="CSDN"><i class="iconfont icon-csdn card_icon_csdn"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">正在考研备考中💦</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#DQL%E8%AF%AD%E8%A8%80"><span class="toc-number">1.</span> <span class="toc-text">DQL语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#Select%E8%AF%AD%E6%B3%95"><span class="toc-number">1.1.</span> <span class="toc-text">Select语法</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%B5%8B%E8%AF%95%E7%8E%AF%E5%A2%83%E6%90%AD%E5%BB%BA"><span class="toc-number">1.2.</span> <span class="toc-text">测试环境搭建</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%8C%87%E5%AE%9A%E6%9F%A5%E8%AF%A2%E5%AD%97%E6%AE%B5"><span class="toc-number">2.</span> <span class="toc-text">指定查询字段</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#AS-%E5%AD%90%E5%8F%A5"><span class="toc-number">2.1.</span> <span class="toc-text">AS 子句</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DISTINCT%E5%85%B3%E9%94%AE%E5%AD%97"><span class="toc-number">2.2.</span> <span class="toc-text">DISTINCT关键字</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BD%BF%E7%94%A8%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%9A%84%E5%88%97"><span class="toc-number">2.3.</span> <span class="toc-text">使用表达式的列</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#where%E6%9D%A1%E4%BB%B6%E8%AF%AD%E5%8F%A5"><span class="toc-number">3.</span> <span class="toc-text">where条件语句</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E9%80%BB%E8%BE%91%E6%93%8D%E4%BD%9C%E7%AC%A6"><span class="toc-number">3.1.</span> <span class="toc-text">逻辑操作符</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%AF%94%E8%BE%83%E6%93%8D%E4%BD%9C%E7%AC%A6"><span class="toc-number">3.2.</span> <span class="toc-text">比较操作符</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">4.</span> <span class="toc-text">连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#JOIN-%E5%AF%B9%E6%AF%94"><span class="toc-number">4.1.</span> <span class="toc-text">JOIN 对比</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E8%87%AA%E8%BF%9E%E6%8E%A5"><span class="toc-number">5.</span> <span class="toc-text">自连接</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%8E%92%E5%BA%8F%E5%92%8C%E5%88%86%E9%A1%B5"><span class="toc-number">6.</span> <span class="toc-text">排序和分页</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">7.</span> <span class="toc-text">子查询</span></a></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/page/project01.html" title="前端实例🥳响应式网站首页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@c19917500ab083c77c7613263ba7ee74d5a08ae6/2021/04/30/469f30b141d73fa0fc4c962662d5813f.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="前端实例🥳响应式网站首页"/></a><div class="content"><a class="title" href="/page/project01.html" title="前端实例🥳响应式网站首页">前端实例🥳响应式网站首页</a><time datetime="2021-04-30T11:50:53.094Z" title="发表于 2021-04-30 19:50:53">2021-04-30</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/WeChatpay.html" title="微信支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@f2f670b92ea149650ffa7834354fc90284f2f44a/2021/04/29/5bdc9b381a06193d27cf2fb7c2fb608a.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="微信支付对接"/></a><div class="content"><a class="title" href="/page/WeChatpay.html" title="微信支付对接">微信支付对接</a><time datetime="2021-04-29T12:20:48.070Z" title="发表于 2021-04-29 20:20:48">2021-04-29</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Alipay.html" title="支付宝支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61a9b6a6e09e4bda38bb08e3104b717885beaee5/2021/04/29/c3fa51f9cf14e90d9e5a7aa8814dd041.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="支付宝支付对接"/></a><div class="content"><a class="title" href="/page/Alipay.html" title="支付宝支付对接">支付宝支付对接</a><time datetime="2021-04-27T16:00:00.000Z" title="发表于 2021-04-28 00:00:00">2021-04-28</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Pagination+PageHelper实现分页"/></a><div class="content"><a class="title" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页">Vue.js前端框架🎯Pagination+PageHelper实现分页</a><time datetime="2021-04-26T14:48:39.701Z" title="发表于 2021-04-26 22:48:39">2021-04-26</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Vue-Element-admin模版"/></a><div class="content"><a class="title" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版">Vue.js前端框架🎯Vue-Element-admin模版</a><time datetime="2021-04-19T16:00:00.000Z" title="发表于 2021-04-20 00:00:00">2021-04-20</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By 小李博客</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">🎯Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">🎉Butterfly</a></div><div class="footer_custom_text"><span><a style="margin-inline:5px" target="_blank" href="https://hexo.io/"><img src="https://img.shields.io/badge/Frame-Hexo-blue?style=flat&logo=hexo" title="博客框架为Hexo"></a><a style="margin-inline:5px" target="_blank" href="https://butterfly.js.org/"><img src="https://img.shields.io/badge/Theme-Butterfly-6513df?style=flat&logo=bitdefender" title="主题采用butterfly"></a><a style="margin-inline:5px" target="_blank" href="https://www.jsdelivr.com/"><img src="https://img.shields.io/badge/CDN-jsDelivr-orange?style=flat&logo=jsDelivr" title="本站使用JsDelivr为静态资源提供CDN加速"></a><a style="margin-inline:5px" target="_blank" href="https://vercel.com/ "><img src="https://img.shields.io/badge/Hosted-Vercel-brightgreen?style=flat&logo=Vercel" title="本站采用双线部署，默认线路托管于Vercel"></a><a style="margin-inline:5px" target="_blank" href="https://coding.net/ "><img src="https://img.shields.io/badge/Hosted-Coding-0cedbe?style=flat&logo=Codio" title="本站采用双线部署，联通线路托管于Coding"></a><a style="margin-inline:5px" target="_blank" href="https://github.com/"><img src="https://img.shields.io/badge/Source-Github-d021d6?style=flat&logo=GitHub" title="本站项目由Gtihub托管"></a><a style="margin-inline:5px" target="_blank" href="http://creativecommons.org/licenses/by-nc-sa/4.0/"><img src="https://img.shields.io/badge/Copyright-BY--NC--SA%204.0-d42328?style=flat&logo=Claris" title="本站采用知识共享署名-非商业性使用-相同方式共享4.0国际许可协议进行许可"></a></span></div><div class="icp"><a target="_blank" rel="noopener" href="https://beian.miit.gov.cn/"><img class="icp-icon" src="/img/icp.png" alt="ICP"/><span>湘ICP备2021002541号</span></a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="chat_btn" type="button" title="rightside.chat_btn"><i class="fas fa-sms"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="algolia-search"><div class="search-dialog"><div class="search-dialog__title" id="algolia-search-title">Algolia</div><div id="algolia-input-panel"><div id="algolia-search-input"></div></div><hr/><div id="algolia-search-results"><div id="algolia-hits"></div><div id="algolia-pagination"></div><div id="algolia-stats"></div></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="https://cdn.jsdelivr.net/npm/vue@2.6.11"></script><script src="/js/main.js"></script><script defer src="/js/tw_cn.js"></script><script defer src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module" defer></script><script defer src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script defer src="/js/search/algolia.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    loader: {
      source: {
        '[tex]/amsCd': '[tex]/amscd'
      }
    },
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        addClass: [200,() => {
          document.querySelectorAll('mjx-container:not([display=\'true\']').forEach( node => {
            const target = node.parentNode
            if (!target.classList.contains('has-jax')) {
              target.classList.add('mathjax-overflow')
            }
          })
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><script>if (document.getElementsByClassName('mermaid').length) {
  if (window.mermaidJsLoad) mermaid.init()
  else {
    getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
      window.mermaidJsLoad = true
      mermaid.initialize({
        theme: 'default',
      })
      false && mermaid.init()
    })
  }
}</script><script>(()=>{
  const $countDom = document.getElementById('twikoo-count')
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: ''
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      $countDom.innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const loadTwikoo = (bool = false) => {
    if (typeof twikoo === 'object') {
      init()
      bool && $countDom && setTimeout(getCount,0)
    } else {
      getScript('https://cdn.jsdelivr.net/npm/twikoo@1.3.0/dist/twikoo.all.min.js').then(()=> {
        init()
        bool && $countDom && setTimeout(getCount,0)
      })
    }
  }

  if ('Twikoo' === 'Twikoo' || !true) {
    if (true) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo(true)
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script defer src="//lib.baomitu.com/jquery/3.5.1/jquery.min.js"></script><script defer src="https://myhkw.cn/api/player/160561664166" id="myhk" key="160561664166" m="1"></script><div><canvas id="snow" style="position:fixed;top:0;left:0;width:100%;height:100%;z-index:99999;pointer-events:none"></canvas></div><script>const notMobile = (!(navigator.userAgent.match(/(phone|pad|pod|iPhone|iPod|ios|iPad|Android|Mobile|BlackBerry|IEMobile|MQQBrowser|JUC|Fennec|wOSBrowser|BrowserNG|WebOS|Symbian|Windows Phone)/i)));</script><scrip async type="text/javascript" src="https://cdn.jsdelivr.net/gh/Candinya/Kratos-Rebirth@latest/source/js/snow.min.js"></scrip><scrip defer src="https://cdn.jsdelivr.net/npm/hexo-theme-volantis@latest/source/js/issues.min.js"></scrip><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><script>var gitcalendar = new Vue({
  el: '#gitcalendar',
  data: {
    simplemode: true, 
    user: 'xiaoliblog',
    fixed: 'fixed',
    px: 'px',
    x: '',
    y: '',
    span1: '',
    span2: '',
    month: ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
    monthchange: [],
    oneyearbeforeday: '',
    thisday: '',
    amonthago: '',
    aweekago: '',
    weekdatacore: 0,
    datacore: 0,
    total: 0,
    datadate: '',
    data: [],
    positionplusdata: [],
    firstweek: [],
    lastweek: [],
    beforeweek: [],
    thisweekdatacore: 0,
    mounthbeforeday: 0,
    mounthfirstindex: 0,
    crispedges: 'crispedges',
    thisdayindex: 0,
    amonthagoindex: 0,
    amonthagoweek: [],
    firstdate: [],
    first2date: [],
    montharrbefore: [],
    monthindex: 0,
    color: ['#ebedf0', '#f1f8ff', '#dbedff', '#c8e1ff', '#79b8ff', '#2188ff', '#0366d6', '#005cc5', '#044289', '#032f62', '#05264c']
  },
  methods: {
    selectStyle(data, event) {
      document.querySelector('.angle-wrapper').style.display = 'block'
      this.span1 = data.date;
      this.span2 = data.count;
      this.x = event.clientX - 100;
      this.y = event.clientY - 60
    },
    outStyle() {
      document.querySelector('.angle-wrapper').style.display = 'none'
    },
    thiscolor(x) {
      if (x === 0) {
        let i = parseInt(x / 2);
        return this.color[0]
      } else if (x < 2) {
        return this.color[1]
      } else if (x < 20) {
        let i = parseInt(x / 2);
        return this.color[i]
      } else {
        return this.color[9]
      }
    },
  }
});
var apiurl = 'python-github-calendar-api-ruby.vercel.app' ? 'https://python-github-calendar-api-ruby.vercel.app/api?' : 'https://githubapi.ryanchristian.dev/user/'
var githubapiurl = apiurl + gitcalendar.user;
//canvas绘图
function responsiveChart() {
  let c = document.getElementById("gitcanvas");
  if (c) {
    let cmessage = document.getElementById("gitmessage");
    let ctx = c.getContext("2d");
    c.width = document.getElementById("gitcalendarcanvasbox").offsetWidth;
    let linemaxwitdh = 0.96 * c.width / gitcalendar.data.length;
    c.height = 9 * linemaxwitdh;
    let lineminwitdh = 0.8 * linemaxwitdh;
    let setposition = {
      x: 0.02 * c.width,
      y: 0.025 * c.width
    };
    for (let week in gitcalendar.data) {
      weekdata = gitcalendar.data[week];
      for (let day in weekdata) {
        let dataitem = {
          date: "",
          count: "",
          x: 0,
          y: 0
        };
        gitcalendar.positionplusdata.push(dataitem);
        ctx.fillStyle = gitcalendar.thiscolor(weekdata[day].count);
        setposition.y = Math.round(setposition.y * 100) / 100;
        dataitem.date = weekdata[day].date;
        dataitem.count = weekdata[day].count;
        dataitem.x = setposition.x;
        dataitem.y = setposition.y;
        ctx.fillRect(setposition.x, setposition.y, lineminwitdh, lineminwitdh);
        setposition.y = setposition.y + linemaxwitdh
      };
      setposition.y = 0.025 * c.width;
      setposition.x = setposition.x + linemaxwitdh
    };
    ctx.font = "600  Arial";
    ctx.fillStyle = '#aaa';
    ctx.fillText("日", 0, 1.9 * linemaxwitdh);
    ctx.fillText("二", 0, 3.9 * linemaxwitdh);
    ctx.fillText("四", 0, 5.9 * linemaxwitdh);
    ctx.fillText("六", 0, 7.9 * linemaxwitdh);
    let monthindexlist = c.width / 24;
    for (let index in gitcalendar.monthchange) {
      ctx.fillText(gitcalendar.monthchange[index], monthindexlist, 0.7 * linemaxwitdh);
      monthindexlist = monthindexlist + c.width / 12
    };
    cmessage.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
    };
    c.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
      getMousePos(c, event);
    };

    function getMousePos(canvas, event) {
      var rect = canvas.getBoundingClientRect();
      var x = event.clientX - rect.left * (canvas.width / rect.width);
      var y = event.clientY - rect.top * (canvas.height / rect.height);
      //console.log("x:"+x+",y:"+y);
      for (let item of gitcalendar.positionplusdata) {
        let lenthx = x - item.x;
        let lenthy = y - item.y;
        //console.log(lenthx,lenthy);
        if (0 < lenthx && lenthx < lineminwitdh) {
          if (0 < lenthy && lenthy < lineminwitdh) {
            //console.log(item.date,item.count)
            document.querySelector('.angle-wrapper').style.display = 'block'
            gitcalendar.span1 = item.date;
            gitcalendar.span2 = item.count;
            gitcalendar.x = event.clientX - 100;
            gitcalendar.y = event.clientY - 60
          }
        }
        //if(0< x - item.x <lineminwitdh&&0< y - item.y <lineminwitdh){
        //console.log(item.count,item.date);
        //}
      }
    }
  }
}
//数据统计算法
function addlastmonth() {
  if (gitcalendar.thisdayindex === 0) {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweekcore(48);
    gitcalendar.thisweekdatacore += gitcalendar.firstdate[6].count;
    gitcalendar.amonthago = gitcalendar.firstdate[6].date
  } else {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweek2core();
    gitcalendar.amonthago = gitcalendar.first2date[gitcalendar.thisdayindex - 1].date
  }
};

function thisweek2core() {
  for (let i = gitcalendar.thisdayindex - 1; i < gitcalendar.first2date.length; i++) {
    gitcalendar.thisweekdatacore += gitcalendar.first2date[i].count
  }
};

function thisweekcore(index) {
  for (let item of gitcalendar.data[index]) {
    gitcalendar.thisweekdatacore += item.count
  }
};

function addlastweek() {
  for (let item of gitcalendar.lastweek) {
    gitcalendar.weekdatacore += item.count
  }
};

function addbeforeweek() {
  for (let i = gitcalendar.thisdayindex; i < gitcalendar.beforeweek.length; i++) {
    gitcalendar.weekdatacore += gitcalendar.beforeweek[i].count
  }
};

function addweek(data) {
  if (gitcalendar.thisdayindex === 6) {
    gitcalendar.aweekago = gitcalendar.lastweek[0].date;
    addlastweek()
  } else {
    lastweek = data.contributions[51];
    gitcalendar.aweekago = lastweek[gitcalendar.thisdayindex + 1].date;
    addlastweek();
    addbeforeweek()
  }
}

fetch(githubapiurl)
  .then(data => data.json())
  .then(data => {
    gitcalendar.data = data.contributions;
    gitcalendar.total = data.total;
    gitcalendar.first2date = gitcalendar.data[48];
    gitcalendar.firstdate = gitcalendar.data[47];
    gitcalendar.firstweek = data.contributions[0];
    gitcalendar.lastweek = data.contributions[52];
    gitcalendar.beforeweek = data.contributions[51];
    gitcalendar.thisdayindex = gitcalendar.lastweek.length - 1;
    gitcalendar.thisday = gitcalendar.lastweek[gitcalendar.thisdayindex].date;
    gitcalendar.oneyearbeforeday = gitcalendar.firstweek[0].date;
    gitcalendar.monthindex = gitcalendar.thisday.substring(5, 7) * 1;
    gitcalendar.montharrbefore = gitcalendar.month.splice(gitcalendar.monthindex, 12 - gitcalendar.monthindex);
    gitcalendar.monthchange = gitcalendar.montharrbefore.concat(gitcalendar.month);
    addweek(data);
    addlastmonth();
    responsiveChart();
  })
  .catch(function(error) {
    console.log(error);
  });

//手机版更换为svg绘制
if (document.getElementById("gitcalendarcanvasbox").offsetWidth < 500) {
  gitcalendar.simplemode = false
}

//当改变窗口大小时重新绘制canvas
window.onresize = function() {
  if (gitcalendar.simplemode) responsiveChart()
}

//解决滚动滑轮时出现的标签显示
window.onscroll = function() {
  if (document.querySelector('.angle-wrapper')) {
    document.querySelector('.angle-wrapper').style.display = 'none'
  }
};</script></div><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"log":false,"model":{"jsonPath":"/live2dw/assets/hijiki.model.json"},"display":{"position":"right","width":150,"height":300},"mobile":{"show":true},"react":{"opacity":0.7}});</script></body></html>